/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.comfacauca.xmlc2.dao;

import com.comfacauca.xmlc2.entidades.Acciones;
import com.comfacauca.xmlc2.entidades.Actividad;
import com.comfacauca.xmlc2.entidades.Avance;
import com.comfacauca.xmlc2.entidades.Comentario;
import com.comfacauca.xmlc2.entidades.Iteracion;
import com.comfacauca.xmlc2.entidades.Proyecto;
import com.comfacauca.xmlc2.entidades.Requerimiento;
import com.comfacauca.xmlc2.entidades.RequerimientosVw;
import com.comfacauca.xmlc2.entidades.Responsable;
import com.comfacauca.xmlc2.servicio.ProyectosI;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.NoResultException;
import javax.persistence.Persistence;
import javax.persistence.Query;


/**
 *
 * @author eorozco
 */
public class ProyectosDAO implements ProyectosI{

          
private static final String PERSISTENCE_UNIT_NAME = "comPU";
private static EntityManagerFactory factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);

    
    
    @Override
    public void setProyecto(Proyecto proyecto) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        try {
        
        em.getTransaction().begin();
        em.merge(proyecto);
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
            
        
    }

    @Override
    public void setIteracion(Iteracion iteracion) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        try {
        
        em.getTransaction().begin();
        em.merge(iteracion);
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
    }

    @Override
    public Requerimiento setRequerimiento(Requerimiento requerimiento) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        int id = 0;
        try {
        
//        em.getTransaction().begin();
//        em.merge(requerimiento);
//        em.getTransaction().commit();
        
        
        
        em.getTransaction().begin();

        requerimiento = em.merge(requerimiento); 
        //requerimiento.getId(); 
        em.refresh(requerimiento);
        
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
        
        return requerimiento;
    }

    @Override
    public void updateConsecutivo(Proyecto proyecto) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
            proyecto.setConsecutivo(proyecto.getConsecutivo()+1);
            em.merge(proyecto);
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
    }

    @Override
    public Iteracion getIteracion(Iteracion iteracion) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        em.getTransaction().begin();
            Iteracion it = (Iteracion) em.createNamedQuery("Iteracion.findById").setParameter("id", iteracion.getId()).getSingleResult();
        em.getTransaction().commit();
        
        return it;
    }

    @Override
    public RequerimientosVw getRequerimiento(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        RequerimientosVw req;
        
        try{
        em.getTransaction().begin();
             req = (RequerimientosVw) em.createNamedQuery("RequerimientosVw.findById").setParameter("id", id).getSingleResult();
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
        
        return req;
    }

    @Override
    public Responsable validarIngresoBasico(String usuario, String clave) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        Responsable resp = null;
        
        try{
        em.getTransaction().begin();
        try{
              resp = (Responsable)em.createQuery("SELECT r FROM Responsable r WHERE r.login=:login and r.clave=:clave")
                              .setParameter("login", usuario)
                              .setParameter("clave", clave)
                              .getSingleResult(); 
        }catch(NoResultException e){
            e.printStackTrace();
        }
             
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
        
        
        return resp;
    }

    @Override
    public List traerSubs(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();        
        List lista;
        try{        
        
        Query query = em.createQuery("SELECT r FROM Requerimiento r where id_requerimiento=:id").setParameter("id", id);        
            lista = query.getResultList();     
        }finally{
            em.close();
        }
        return lista;
    }

    @Override
    public int setComentario(Comentario comentario) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        int id = 0;
        
        try {
        
        em.getTransaction().begin();
        em.persist(comentario);
        em.flush();
        id = comentario.getId();
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
        
        return id;
    }

    @Override
    public List traerComs(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();     
        List lista;
        try{
        Query query = em.createQuery("SELECT r FROM Comentario r where id_requerimiento=:id order by id desc").setParameter("id", id);        
            lista = query.getResultList();     
        }finally{
            if(em != null)
                em.close();
        }
        
        return lista;
    }

    @Override
    public List traerRequerimientos(String parameter) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();    
        List lista;
        try{
        Query query = em.createQuery("SELECT r FROM Requerimiento r where codigo LIKE :term ").setParameter("term", parameter+"%");        
            lista = query.getResultList();     
        }finally{
            if(em != null)    
                em.close();
        }
        
        return lista;
    }

    @Override
    public void setAvance(Avance avance) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        try {
        
        em.getTransaction().begin();
        em.merge(avance);
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
    }

    @Override
    public void setAcciones(Acciones acciones) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        try {
        
        em.getTransaction().begin();
        em.merge(acciones);
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
    }

    @Override
    public void updateEstado(Integer idRequerimiento) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
        Query query = em.createQuery(
            "UPDATE Requerimiento SET id_estado = 3 where id=:id").setParameter("id", idRequerimiento);        
        int updateCount = query.executeUpdate();
        em.getTransaction().commit();
        }finally{
            if(em != null)    
                em.close();
        }
        
    }

    @Override
    public void updateEstadoRes(int idRequerimiento) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
        Query query = em.createQuery(
            "UPDATE Requerimiento SET id_estado = 2 where id=:id").setParameter("id", idRequerimiento);        
        int updateCount = query.executeUpdate();
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
    }


    @Override
    public void updateResponsable(int responsable, int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
        Query query = em.createQuery(
            "UPDATE Requerimiento SET id_responsable = "+responsable+" where id=:id").setParameter("id", id);        
        int updateCount = query.executeUpdate();
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
        
    }

    @Override
    public Object[] traerAvance(int id, int id_autor) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();        
        
        Object[] result = null;
        
        try{
        Query q = em.createNativeQuery("SELECT " +
                                        "  avance.fecha," +
                                        "  avance.id_requerimiento," +
                                        "  avance.valor," +
                                        "  etapa.descripcion," +
                                        "  avance.id_autor," +
                                        "  0" +
                                        " FROM" +
                                        "  avance" +
                                        "  INNER JOIN responsable ON (avance.id_autor = responsable.id)" +
                                        "  INNER JOIN requerimiento ON (avance.id_requerimiento = requerimiento.id)" +
                                        "  INNER JOIN etapa ON (requerimiento.id_etapa = etapa.id)" +
                                        " where avance.id_requerimiento = ?1 and avance.id_autor= ?2" +
                                        " ORDER BY" +
                                        " avance.fecha desc" +
                                        " LIMIT 1 ");
        q.setParameter(1, id);
        q.setParameter(2, id_autor);
        
        try{
            result = (Object[])q.getSingleResult();
        }catch(NoResultException nre){
            nre.printStackTrace();
        }
        }finally{
            if(em != null)    
                em.close();
        }
        
        
        return result;
    }
    
    
    @Override
    public Object[] traerAvanceReq(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();        
        
        Object[] result = null;
        
        try{
        Query q = em.createNativeQuery("SELECT " +
                                        "  avance.fecha," +
                                        "  avance.id_requerimiento," +
                                        "  avance.valor," +
                                        "  1," +
                                        "  avance.id_autor," +
                                        "  0" +
                                        " FROM" +
                                        "  avance" +
                                        "  INNER JOIN responsable ON (avance.id_autor = responsable.id)" +
                                        "  INNER JOIN etapa ON (responsable.id_etapa = etapa.id)"
                                        + " where id_requerimiento = ?1" +
                                        " ORDER BY" +
                                        " avance.fecha desc" +
                                        " LIMIT 1 ");
        q.setParameter(1, id);        
        
        try{
            result = (Object[])q.getSingleResult();
        }catch(NoResultException nre){
            nre.printStackTrace();
        }
        
        }finally{
            if(em != null)
                em.close();
        }
        
        return result;
    }
    
    
    
    @Override
    public Object traerAvanceTiempo(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();        
        Object result = null;               
        
        try{
        Query q = em.createNativeQuery("SELECT " +                                                                                
                                        "  sum(avance.tiempo) tiempo" +
                                        " FROM" +
                                        "  avance" +                                                                                
                                        " where id_requerimiento = ?1"
                                        );
        q.setParameter(1, id);        
        
        try{
            result = (Object)q.getSingleResult();
        }catch(NoResultException nre){
            nre.printStackTrace();
        }
        }finally{
            if(em != null)
                em.close();
        }
        
        return result;
    }
    
    
    
    
    @Override
    public void setResponsable(Responsable responsable) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        try {
        
        em.getTransaction().begin();
        em.merge(responsable);
        em.getTransaction().commit();        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        } 
    }

    @Override
    public List traerAcciones(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager(); 
        List lista;
        
        try{
               
        Query query = em.createQuery("SELECT r FROM Acciones r where id_requerimiento=:id order by id desc").setParameter("id", id);        
            lista = query.getResultList();
        }finally{
            if(em != null)
                em.close();
        }
        
        return lista;
    }

    @Override
    public Responsable traerResponsable(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        Responsable resp = null;
        
        try{
        em.getTransaction().begin();
        try{
              resp = (Responsable)em.createQuery("SELECT r FROM Responsable r WHERE r.id=:id")
                              .setParameter("id",id)                              
                              .getSingleResult(); 
        }catch(NoResultException e){
            e.printStackTrace();
        }
             
        em.getTransaction().commit();
        
        }finally{
            if(em != null)
                em.close();
        }
        
        return resp;
    }

    @Override
    public List getItemsUsuario(int usuario) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();       
        List lista;
        try{        
        Query query = em.createQuery("SELECT r FROM RequerimientosVw r where idResponsable=:usuario order by inicioRequerimiento asc").setParameter("usuario", usuario);        
            lista = query.getResultList();     
        }finally{
            if(em != null)
                em.close();
        }
        return lista;
    }

    @Override
    public Object[] traerAvance(int id) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();        
           
        Object result[] = null;
        
        try{
        Query q = em.createNativeQuery("SELECT " +                                                                                
                                        "  avance.valor valor," +
                                        "  avance.tiempo tiempo" +
                                        " FROM" +
                                        "  avance" +                                                                                
                                        " where id_requerimiento = ?1"+
                                         " ORDER BY" +
                                        " avance.fecha desc" +
                                        " LIMIT 1 ");
        q.setParameter(1, id);        
        
        try{
            result = (Object[])q.getSingleResult();
        }catch(NoResultException nre){
            nre.printStackTrace();
        }
        }finally{
            if(em != null)
                em.close();
        }
        
        return result;
    }

    @Override
    public void updateRequerimiento(String tabla, String campo, Date valor, int id) {
         //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
        Query query = em.createQuery(
            "UPDATE "+tabla+" SET "+campo+" = :valor where id=:id").setParameter("id", id).setParameter("valor", valor);        
        int updateCount = query.executeUpdate();
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
    }
  
    @Override
    public void updateRequerimiento(String tabla, String campo, String valor, int id) {
         //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
        Query query = em.createQuery(
            "UPDATE "+tabla+" SET "+campo+" = :valor where id=:id").setParameter("id", id).setParameter("valor", valor);        
        int updateCount = query.executeUpdate();
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
    }

    @Override
    public Object traerSiguiente(int id, int etapa) {
        //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();        
        Object result = null;               
        
        try{
        Query q = em.createNativeQuery("SELECT   " +
                                        " requerimiento.id  " +
                                        " FROM" +
                                        " requerimiento " +
                                        " inner join responsable on (requerimiento.id_responsable=responsable.id)" +
                                        " where requerimiento.id_requerimiento=?" +
                                        " and requerimiento.id_etapa = ?"
                                        );
        q.setParameter(1, id);   
        q.setParameter(2, etapa+1);
        
        try{
            result = (Object)q.getSingleResult();
        }catch(NoResultException nre){
            nre.printStackTrace();
        }
        }finally{
            if(em != null)
                em.close();
        }
        
        return result;
    }

    @Override
    public void updateEtapa(int id) {
        EntityManager em = factory.createEntityManager();
        
        try{
        em.getTransaction().begin();
        Query query = em.createQuery(
            "UPDATE Requerimiento SET id_estado = 1 where id=:id").setParameter("id", id);        
        int updateCount = query.executeUpdate();
        em.getTransaction().commit();
        }finally{
            if(em != null)
                em.close();
        }
    }

    @Override
    public Requerimiento getRequerimientoObj(int id) {
         //factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        
        Requerimiento resp = null;
        
        try{
        em.getTransaction().begin();
        try{
              resp = (Requerimiento)em.createQuery("SELECT r FROM Requerimiento r WHERE r.id=:id")
                              .setParameter("id",id)                              
                              .getSingleResult(); 
        }catch(NoResultException e){
            e.printStackTrace();
        }
             
        em.getTransaction().commit();
        
        }finally{
            if(em != null)
                em.close();
        }
        
        return resp;
    }

    @Override
    public void setActividad(Actividad actividad) {
        EntityManager em = factory.createEntityManager();
        try {
        
        em.getTransaction().begin();
        em.merge(actividad);
        em.getTransaction().commit();
        
       
        } catch (SecurityException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalStateException ex) {
            Logger.getLogger(ProyectosDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //close the em to release any resources held up by the persistebce provider
            if(em != null) {
                em.close();
            }
        }    
    }

    @Override
    public float traerAcumulado() {
        EntityManager em = factory.createEntityManager();        
        float result = 0;               
        
        try{
        Query q = em.createNativeQuery("SELECT " +                                                                                
                                        "  sum(actividad.tiempo) tiempo" +
                                        " FROM" +
                                        "  actividad" +                                                                                
                                        " where fecha = curdate()"
                                        );
             
        
        try{
            result = Float.parseFloat(q.getSingleResult().toString());
        }catch(NoResultException nre){
            nre.printStackTrace();
        }
        }finally{
            if(em != null)
                em.close();
        }
        
        return result;
    }
}
